﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using jnagr.DBUtility;//Please add references
namespace jnagr.DAL
{
    /// <summary>
    /// 数据访问类:devdays
    /// </summary>
    public partial class devdays
    {
        public devdays()
        { }
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ddid", "devdays");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ddid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from devdays");
            strSql.Append(" where ddid=@ddid");
            SqlParameter[] parameters = {
					new SqlParameter("@ddid", SqlDbType.Int,4)
			};
            parameters[0].Value = ddid;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        public int Exists(jnagr.Model.devdays model)
        {
            int r = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ddid from devdays");
            strSql.Append(" where devtype=@devtype");
            SqlParameter[] parameters = {
					new SqlParameter("@devtype", SqlDbType.VarChar,50)
			};
            parameters[0].Value = model.devtype;

            object o = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (o != null)
            {
                int.TryParse(o.ToString(), out r);
            }
            return r;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(jnagr.Model.devdays model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into devdays(");
            strSql.Append("devtype,interday)");
            strSql.Append(" values (");
            strSql.Append("@devtype,@interday)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@devtype", SqlDbType.VarChar,50),
					new SqlParameter("@interday", SqlDbType.Int,4)};
            parameters[0].Value = model.devtype;
            parameters[1].Value = model.interday;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(jnagr.Model.devdays model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update devdays set ");
            strSql.Append("devtype=@devtype,");
            strSql.Append("interday=@interday");
            strSql.Append(" where ddid=@ddid");
            SqlParameter[] parameters = {
					new SqlParameter("@devtype", SqlDbType.VarChar,50),
					new SqlParameter("@interday", SqlDbType.Int,4),
					new SqlParameter("@ddid", SqlDbType.Int,4)};
            parameters[0].Value = model.devtype;
            parameters[1].Value = model.interday;
            parameters[2].Value = model.ddid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ddid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from devdays ");
            strSql.Append(" where ddid=@ddid");
            SqlParameter[] parameters = {
					new SqlParameter("@ddid", SqlDbType.Int,4)
			};
            parameters[0].Value = ddid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ddidlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from devdays ");
            strSql.Append(" where ddid in (" + ddidlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public jnagr.Model.devdays GetModel(int ddid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ddid,devtype,interday from devdays ");
            strSql.Append(" where ddid=@ddid");
            SqlParameter[] parameters = {
					new SqlParameter("@ddid", SqlDbType.Int,4)
			};
            parameters[0].Value = ddid;

            jnagr.Model.devdays model = new jnagr.Model.devdays();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public jnagr.Model.devdays DataRowToModel(DataRow row)
        {
            jnagr.Model.devdays model = new jnagr.Model.devdays();
            if (row != null)
            {
                if (row["ddid"] != null && row["ddid"].ToString() != "")
                {
                    model.ddid = int.Parse(row["ddid"].ToString());
                }
                if (row["devtype"] != null)
                {
                    model.devtype = row["devtype"].ToString();
                }
                if (row["interday"] != null && row["interday"].ToString() != "")
                {
                    model.interday = int.Parse(row["interday"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ddid,devtype,interday ");
            strSql.Append(" FROM devdays ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ddid,devtype,interday ");
            strSql.Append(" FROM devdays ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM devdays ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ddid desc");
            }
            strSql.Append(")AS Row, T.*  from devdays T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "devdays";
            parameters[1].Value = "ddid";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 获得各设备的运行监控
        /// </summary>
        public DataSet GetDevWork(DateTime sdate, DateTime edate, string devtype, bool isautodate, string sebm, string where, string sort, string location)
        {
            if (isautodate)
            {
                int i = GetInter(devtype);
                sdate = edate.AddDays(-i);

            }
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqldev = new StringBuilder();
            strSql.Append("SELECT     mt.DevID,mt.devok, dbo.userinfo.uname, dbo.userinfo.utruename, dbo.userinfo.uphone, dbo.userinfo.uemail,dbo.userinfo.uid,mt.devname,mt.mjd,mt.mwd");
            strSql.Append(" FROM         dbo.userinfo INNER JOIN");
            strSql.Append(" dbo.userdevinfo ON dbo.userinfo.uid = dbo.userdevinfo.uid right OUTER JOIN");
            strSql.Append(" (SELECT     distinct dbo.devinfo.DevID,devname,mjd,mwd,case  when dt is null then '0' else '1' end as devok");
            strSql.Append(" FROM         dbo.devinfo LEFT OUTER JOIN(");
            if (devtype == "CBD")
            {
                strSqldev.Append(" SELECT convert(varchar(30),devID) as devID, COUNT(*) as dt FROM dbstatus.dbo.t_cbdStatus");
                strSqldev.AppendFormat(" WHERE updateTime > '{0}' AND updateTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());            
                strSqldev.Append(" group by devID)");
            }
            else if (devtype == "YBQ")
            {
                strSqldev.Append(" SELECT convert(varchar(30),devID) as devID, COUNT(*) as dt FROM [dbstatus].[dbo].[t_ybqstatus] ");
                strSqldev.AppendFormat(" WHERE updateTime > '{0}' AND updateTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by devID)");                
            }
            else if (devtype == "CBJ")
            {
                strSqldev.Append(" SELECT toolsNums as devID, COUNT(*) as dt FROM [ForestPets] ");
                strSqldev.AppendFormat(" WHERE  petStage='成虫' and  inputTime > '{0}' AND inputTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by toolsNums)");                
            }
            else if (devtype == "PET")
            {
                strSqldev.Append(" SELECT toolsNums as devID, COUNT(*) as dt FROM [ForestPets] ");
                strSqldev.AppendFormat(" WHERE  petStage<>'成虫' and  inputTime > '{0}' AND inputTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by toolsNums)");                 
            }
            else if (devtype == "GRA")
            {
                strSqldev.Append(" SELECT toolsNums as devID, COUNT(*) as dt FROM [ForsetGrass] ");
                strSqldev.AppendFormat(" WHERE  inputTime > '{0}' AND inputTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by toolsNums)"); 
                
            }
            else if (devtype == "RAT")
            {
                strSqldev.Append(" SELECT toolsNums as devID, COUNT(*) as dt FROM [ForestRat] ");
                strSqldev.AppendFormat(" WHERE  inputTime > '{0}' AND inputTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by toolsNums)");                
            }
            else if (devtype == "SIC")
            {
                strSqldev.Append(" SELECT toolsNums as devID, COUNT(*) as dt FROM [ForestSickness] ");
                strSqldev.AppendFormat(" WHERE  inputTime > '{0}' AND inputTime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by toolsNums)");                
            }
            else if (devtype == "BPY")
            {
                strSqldev.Append(" SELECT devid as devID, COUNT(*) as dt FROM [cxinfo] ");
                strSqldev.AppendFormat(" WHERE  updatetime > '{0}' AND updatetime <= '{1}' ", sdate.ToShortDateString(), edate.ToShortDateString());
                strSqldev.Append(" group by devid)");                
            }
            else if (devtype == "TSQ" || devtype == "XQH")
            {
                return GetWork(sdate, edate, devtype, isautodate, sebm, where, sort,location);
            }
            strSql.Append(strSqldev.ToString());
            strSql.Append(" as vw ON dbo.devinfo.DevID = vw.devID");

            strSql.AppendFormat(" WHERE dbo.devinfo.DevType = '{0}' ", devtype);
            if (sebm != "" && sebm != "0")
            {

                if (sebm.Length == 6)
                {

                    if (sebm.EndsWith("0000"))
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm.Substring(0, 2));
                    }
                    else if (sebm.EndsWith("00"))
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm.Substring(0, 4));
                    }
                    else
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm);
                    }
                }
                else
                {
                    strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm);
                }
            }
            else
            {
                strSql.AppendFormat("  and DevLocation like '%{0}%' ", location);
            }         
            strSql.Append(" ) as  mt ON dbo.userdevinfo.devid = mt.DevID ");
            if (where != "")
                strSql.Append("where " + where );
            if(sort!="")
                strSql.Append(" order by " + sort);
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// XQH\TSQ工作
        /// </summary>
        /// <param name="devtype"></param>
        /// <returns></returns>
        public DataSet GetWork(DateTime sdate, DateTime edate, string devtype, bool isautodate, string sebm, string where, string sort, string location)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT dbo.devinfo.DevID,'0' as devok,dbo.userinfo.uname, dbo.userinfo.utruename, dbo.userinfo.uphone, dbo.userinfo.uemail,dbo.devinfo.devname,mjd,mwd,dbo.userinfo.uid FROM  dbo.userdevinfo INNER JOIN dbo.userinfo ON dbo.userdevinfo.uid = dbo.userinfo.uid RIGHT OUTER JOIN   dbo.devinfo ON dbo.userdevinfo.devid = dbo.devinfo.DevID");
            strSql.AppendFormat(" where devtype='{0}' ", devtype);
            if (sebm != "" && sebm != "0")
            {
                if (sebm.Length == 6)
                {

                    if (sebm.EndsWith("0000"))
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm.Substring(0, 2));
                    }
                    else if (sebm.EndsWith("00"))
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm.Substring(0, 4));
                    }
                    else
                    {
                        strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm);
                    }
                }
                else
                {
                    strSql.AppendFormat("and DevOwnerID like '%{0}%'  ", sebm);
                }
            }
            else
            {
                strSql.AppendFormat("  and DevLocation like '%{0}%' ", location);
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            string devid = "";
            if (devtype == "XQH")
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    devid = ds.Tables[0].Rows[i]["DevID"].ToString();
                    strSql.Clear();
                    strSql.AppendFormat("SELECT count(*) FROM [PGData].[dbo].[{0}] AS t WHERE  datetime > '{1}' AND datetime <= '{2}'", devid, sdate.ToShortDateString(), edate.ToShortDateString());
                    try
                    {
                        object o = DbHelperSQL.GetSingle(strSql.ToString());
                        if (o != null)
                        {
                            if (o.ToString() != "0")
                            {
                                ds.Tables[0].Rows[i]["devok"] = "1";
                            }
                        }
                    }
                    catch (Exception)
                    {
                    }
                }

            }
            else if (devtype == "TSQ")
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {

                    devid = ds.Tables[0].Rows[i]["DevID"].ToString();
                    strSql.Clear();
                    strSql.AppendFormat("SELECT count(*) FROM [SqData].[dbo].[{0}] AS t WHERE  datetime > '{1}' AND datetime <= '{2}'", devid, sdate.ToShortDateString(), edate.ToShortDateString());
                    try
                    {
                        object o = DbHelperSQL.GetSingle(strSql.ToString());
                        if (o != null)
                        {
                            if (o.ToString() != "0")
                            {
                                ds.Tables[0].Rows[i]["devok"] = "1";
                            }
                        }
                    }
                    catch (Exception)
                    {
                    }
                }
            }
            DataView dv = new DataView();
            dv.Table = ds.Tables[0];
            if (where != "")
                dv.RowFilter = where;
            if (sort != "")
                dv.Sort = sort;
            DataSet dso = new DataSet();
            dso.Tables.Add(dv.ToTable());
            return dso;
        }
        /// <summary>
        /// 取得设备的间隔时间
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int GetInter(string devtype)
        {
            int r = 1;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select interday from devdays");
            strSql.Append(" where devtype=@devtype");
            SqlParameter[] parameters = {
					new SqlParameter("@devtype", SqlDbType.VarChar,50)
			};
            parameters[0].Value = devtype;

            object o = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (o != null)
            {
                int.TryParse(o.ToString(), out r);
            }
            return r;
        }
        #endregion  ExtensionMethod
    }
}

